﻿Imports vsto = Microsoft.Office.Interop.Excel

Imports Microsoft.Office.Interop.Excel.XlPivotTableSourceType
Imports Microsoft.Office.Interop.Excel.XlCmdType
Imports Microsoft.Office.Interop.Excel.XlPivotTableVersionList
Imports Microsoft.Office.Interop.Excel.XlCellInsertionMode
Imports Microsoft.Office.Interop.Excel.XlListObjectSourceType

Imports Microsoft.Win32

Public Class ExcelHelper
    Implements IDisposable

    Private Shared Property ConnectionString As String
        Get
            Dim cnn = CType(My.Settings.Properties("ConnectionString").DefaultValue, String)

            If cnn = String.Empty Then
                Dim teststring = InputBox("What SQL Server do you want to use?")
                If Not teststring = String.Empty Then
                    My.Settings.Properties("ConnectionString").DefaultValue = String.Format("ODBC;DRIVER=SQL Server;SERVER={0};Trusted_Connection=Yes", teststring)
                End If
            End If

            Return CType(My.Settings.Properties("ConnectionString").DefaultValue, String)

        End Get
        Set(value As String)
            My.Settings.Properties("ConnectionString").DefaultValue = value
            My.Settings.Save()
        End Set
    End Property

    Public Shared Sub FillList(ByVal actSheet As vsto.Worksheet, ByVal sqlCommandText As String)

        FillList(actSheet, sqlCommandText, "A1")

    End Sub

    Public Shared Sub FillList(ByVal actSheet As vsto.Worksheet, ByVal sqlCommandText As String, ByVal homeRange As String)

        Dim r As vsto.Range = actSheet.Range(homeRange)

        Try

            With actSheet.ListObjects.Add(SourceType:=xlSrcQuery, Source:=ConnectionString, Destination:=r).QueryTable
                .CommandType = xlCmdSql
                .CommandText = sqlCommandText
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .PreserveColumnInfo = True
                .ListObject.DisplayName = "Table_on_" & Replace(actSheet.Name, " ", "_")
                .Refresh(BackgroundQuery:=False)
            End With

            actSheet.ListObjects("Table_on_" & Replace(actSheet.Name, " ", "_")).TableStyle = "TableStyleLight9"

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

    Public Sub FillRange(ByVal actSheet As vsto.Worksheet, ByVal sqlCommandText As String)

        FillRange(actSheet, sqlCommandText, "A1")

    End Sub

    Public Sub FillRange(ByVal actSheet As vsto.Worksheet, ByVal sqlCommandText As String, ByVal homeRange As String)

        Dim r As vsto.Range = actSheet.Range(homeRange)

        With actSheet.QueryTables.Add(Connection:=ConnectionString, Destination:=r)
            .CommandText = sqlCommandText
            .Name = "Query from SQLSERVER"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .Refresh(BackgroundQuery:=False)
        End With

    End Sub

    Public Shared Sub fillPivot(ByVal activeWorkBook As vsto.Workbook, ByVal sqlCommandText As String)

        If sqlCommandText <> String.Empty Then
            With activeWorkBook.PivotCaches.Add(SourceType:=xlExternal)
                .Connection = ConnectionString
                .CommandType = xlCmdSql
                .CommandText = sqlCommandText
                .CreatePivotTable(TableDestination:="R3C1", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10)
            End With
        End If

    End Sub

#Region "IDisposable Support"
    Private disposedValue As Boolean ' To detect redundant calls

    ' IDisposable
    Protected Overridable Sub Dispose(disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
                ' TODO: dispose managed state (managed objects).
            End If

            ' TODO: free unmanaged resources (unmanaged objects) and override Finalize() below.
            ' TODO: set large fields to null.
        End If
        Me.disposedValue = True
    End Sub

    ' TODO: override Finalize() only if Dispose(ByVal disposing As Boolean) above has code to free unmanaged resources.
    'Protected Overrides Sub Finalize()
    '    ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
    '    Dispose(False)
    '    MyBase.Finalize()
    'End Sub

    ' This code added by Visual Basic to correctly implement the disposable pattern.
    Public Sub Dispose() Implements IDisposable.Dispose
        ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub
#End Region

End Class